using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Metadata.Internal;
using MySqlConnector;
using System.Collections.Generic;
using System.Diagnostics;
using System.Threading.Tasks;
using way.mapper;
using way.model;
using way.util;
using static Lucene.Net.Index.SegmentReader;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;

namespace way.service
{
    
    public interface IMenuService
    {
        #region menu
        Result<int> deleteMenuById(int targetId, Result<int> res);
        bool menuAdd(MenuEntity menu);
        Result<int> menuAddHead(string name, Result<int> res);
        Result<int> menuAddChild(int targetId, string newName, Result<int> res);
        Result<int> menuAddAfter(int targetId, string newName, Result<int> res);
        Result<int> menuAddBefore(int targetId, string newName, Result<int> res);
        Result<bool> changeMenuChild(int changeId, int targetId, Result<bool> res);
        Result<bool> changeMenuBefore(int changeId, int targetId, Result<bool> res);
        Result<bool> changeMenuAfter(int changeId, int targetId, Result<bool> res);
        Result<bool> copyMenuChild(int changeId, int targetId, Result<bool> res);
        Result<bool> copyMenuBefore(int changeId, int targetId, Result<bool> res);
        Result<bool> copyMenuAfter(int changeId, int targetId, Result<bool> res);
        Result<int> updateMenuNameById(int targetId,string newName, Result<int> res);
        Result<Dictionary<int, MenuEntity>> queryMenu(Result<Dictionary<int, MenuEntity>> res);
        Result<Dictionary<int,MenuEntity>> queryMenuAround(Result<Dictionary<int, MenuEntity>> res);
        public Result<Dictionary<int, MenuEntity>> queryMenuTop(Result<Dictionary<int, MenuEntity>> res);
        public Result<Dictionary<int, MenuEntity>> queryMenuBottom(Result<Dictionary<int, MenuEntity>> res);
        Result<MenuEntity> queryMenuById(Result<MenuEntity> res);
        Result<List<MenuEntity>> queryMenuSurroundsById(Result<List<MenuEntity>> res);
        Result<List<MenuEntity>> queryMenuChildsById(Result<List<MenuEntity>> res, AppDbContext dbContext);
        Result<List<MenuEntity>> queryMenuChildsById(Result<List<MenuEntity>> res);
        Result<Dictionary<int, List<MenuEntity>>> sortMenuIdMap(Result<Dictionary<int, MenuEntity>> req, Result<Dictionary<int, List<MenuEntity>>> res);
        List<int> GetChildMenuId(AppDbContext dbContext, int upMenuId);
        #endregion

        #region in_core
        Result<List<MenuInCoreEntity>> queryMenuInCoreByMenuId(int menuId,Result<List<MenuInCoreEntity>> res);
        Result<MenuInCoreEntity> AddMenuInCoreByMenuId( Result<MenuInCoreEntity> res);
        Result<MenuInCoreEntity> updateMenuInCoreById( Result<MenuInCoreEntity> res);
        Result<MenuInCoreEntity> deleteMenuInCoreById( Result<MenuInCoreEntity> res);
        Result<int> changeBeforeInCore(int changeId, int targetId, Result<int> res);
        Result<int> changeAfterInCore(int changeId, int targetId, Result<int> res);
        Result<int> addBeforeInCore(MenuInCoreEntity entity, int targetId, Result<int> res);
        Result<int> addAfterInCore(MenuInCoreEntity entity, int targetId, Result<int> res);
        #endregion

        #region 
        Result<List<string>> searchTableByKeyword(string key, Result<List<string>> res);
        #endregion
    }
    public class MenuService: IMenuService
    {
        #region menu
        IMenuMapper mapper = InstanceManage.getMenuMapper();
        MenuCore menuCore = InstanceManage.getMenuCore();

        public Result<int> deleteMenuById(int targetId, Result<int> res) {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                //childMenus Update
                var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                allChildMenuIds.Add(targetId);
                dbContext.menus
                    .Where(m => allChildMenuIds.Contains(m.id))
                    .ExecuteDelete();
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public bool menuAdd(MenuEntity menu)
        {
            using (var conn = new MySqlConnection(DatabaseUtil.getDatabase()))
            {
                
            }
            return true;
        }
        public Result<int> menuAddHead(string name, Result<int> res)
        {
            //table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            //name null check
            if (string.IsNullOrWhiteSpace(name)) return res.setStatus(ResultCode.dataNameNull);
            //init data
            
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //name exist check
                bool nameExsit = dbContext.menus.Any(m => m.up == menuCore.upHead && m.name == name);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //data init
                MenuEntity menu = new MenuEntity
                {
                    name = name,
                    up = menuCore.upHead,
                    level = menuCore.upHead + 1,
                    sort = Convert.ToInt32(dbContext.menus.Where(m => m.up == menuCore.upHead).Select(m => (int?)m.sort).Max()) + 1,
                    create = DateTime.Now,
                };
                //comit
                dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> menuAddChild(int targetId, string newName, Result<int> res)
        {
            //table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            //name null check
            if (string.IsNullOrWhiteSpace(newName)) return res.setStatus(ResultCode.dataNameNull);
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //menu select
                MenuEntity upMenu = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (upMenu == null) { return res.setStatus(ResultCode.menuUpNotFound); }
                //name exist checks
                bool nameExsit = dbContext.menus.Any(m => m.up == upMenu.id && m.name == newName);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //newMenu init
                MenuEntity menu = new MenuEntity
                {
                    name = newName,
                    up = upMenu.id,
                    level = upMenu.level + 1,
                    sort = Convert.ToInt32(dbContext.menus.Where(m => m.up == upMenu.id).Select(m => (int?)m.sort).Max()) + 1,
                    create = DateTime.Now
                };
                //comit
                dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> menuAddAfter(int targetId, string newName, Result<int> res)
        {
            //table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            //name null check
            if (string.IsNullOrWhiteSpace(newName)) return res.setStatus(ResultCode.dataNameNull);
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //menu select
                MenuEntity targetEntity = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (targetEntity == null) { return res.setStatus(ResultCode.menuBeforeNotFound); }
                //name exist check
                bool nameExsit = dbContext.menus.Any(m => m.up == targetEntity.up && m.name == newName);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //menu init
                MenuEntity menu = new MenuEntity
                {
                    name = newName,
                    up = targetEntity.up,
                    level = targetEntity.level,
                    create = DateTime.Now
                };
                var afterChilds = dbContext.menus.Where(m => m.up == menu.up && m.sort > targetEntity.sort).ToList();
                foreach (var menuK in afterChilds)
                {
                    menuK.sort++;
                }
                menu.sort = targetEntity.sort + 1;
                dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> menuAddBefore(int targetId, string newName, Result<int> res)
        {
            //table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            //name null check
            if (string.IsNullOrWhiteSpace(newName)) return res.setStatus(ResultCode.dataNameNull);
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //menu select
                MenuEntity targetEntity = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (targetEntity == null) { return res.setStatus(ResultCode.menuBeforeNotFound); }
                //name exist check
                bool nameExsit = dbContext.menus.Any(m => m.up == targetEntity.up && m.name == newName);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //menu init
                MenuEntity menu = new MenuEntity
                {
                    name = newName,
                    up = targetEntity.up,
                    level = targetEntity.level,
                    create = DateTime.Now
                };
                var afterChilds = dbContext.menus.Where(m => m.up == menu.up && m.sort >= targetEntity.sort).ToList();
                foreach (var menuK in afterChilds)
                {
                    menuK.sort++;
                }
                menu.sort = targetEntity.sort - 1;
                dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> changeMenuChild(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                //changeMenu update
                menu.up = targetMenu.id;
                menu.level = targetMenu.level+1;
                menu.sort = Convert.ToInt32(dbContext.menus.Where(m => m.up == menu.up).Select(m => (int?)m.sort).Max()) + 1;
                menu.update = DateTime.Now;
                //childMenus Update
                var allChildMenuIds = GetChildMenuId(dbContext,menu.id);
                dbContext.menus
                    .Where(m => allChildMenuIds.Contains(m.id))
                    .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m=>m.level + 1));
                
                //commond
                //dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> changeMenuBefore(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                int levelLength = targetMenu.level - menu.level;
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                var targetAfterChilds = dbContext.menus.Where(m => m.up == targetMenu.up && m.sort >= targetMenu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                foreach (var menuK in targetAfterChilds)
                {
                    menuK.sort++;
                }
                //changeMenu update
                menu.up = targetMenu.up;
                menu.level = targetMenu.level;
                menu.sort = targetMenu.sort - 1;
                menu.update = DateTime.Now;
                
                //childMenus Update
                if (levelLength!=0) {
                    var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                    dbContext.menus
                        .Where(m => allChildMenuIds.Contains(m.id))
                        .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m => m.level + levelLength));
                }
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> changeMenuAfter(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId== targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                int levelLength = targetMenu.level - menu.level;
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                var targetAfterChilds = dbContext.menus.Where(m => m.up == targetMenu.up && m.sort > targetMenu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                //childMenus Update
                foreach (var menuK in targetAfterChilds)
                {
                    menuK.sort++;
                }
                //changeMenu update
                menu.up = targetMenu.up;
                menu.level = targetMenu.level;
                menu.sort = targetMenu.sort + 1;
                menu.update = DateTime.Now;
                
                //childMenus Update
                if (levelLength != 0)
                {
                    var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                    dbContext.menus
                        .Where(m => allChildMenuIds.Contains(m.id))
                        .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m => m.level + levelLength));
                }
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> copyMenuChild(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                //changeMenu update
                menu.up = targetMenu.id;
                menu.level = targetMenu.level + 1;
                menu.sort = Convert.ToInt32(dbContext.menus.Where(m => m.up == menu.up).Select(m => (int?)m.sort).Max()) + 1;
                menu.update = DateTime.Now;
                //childMenus Update
                var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                dbContext.menus
                    .Where(m => allChildMenuIds.Contains(m.id))
                    .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m => m.level + 1));

                //commond
                //dbContext.menus.Add(menu);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> copyMenuBefore(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                int levelLength = targetMenu.level - menu.level;
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                var targetAfterChilds = dbContext.menus.Where(m => m.up == targetMenu.up && m.sort >= targetMenu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                foreach (var menuK in targetAfterChilds)
                {
                    menuK.sort++;
                }
                //changeMenu update
                menu.up = targetMenu.up;
                menu.level = targetMenu.level;
                menu.sort = targetMenu.sort - 1;
                menu.update = DateTime.Now;

                //childMenus Update
                if (levelLength != 0)
                {
                    var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                    dbContext.menus
                        .Where(m => allChildMenuIds.Contains(m.id))
                        .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m => m.level + levelLength));
                }
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<bool> copyMenuAfter(int changeId, int targetId, Result<bool> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //
                if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == changeId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //changeMenu exist check
                MenuEntity targetMenu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == targetId);
                if (targetMenu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                int levelLength = targetMenu.level - menu.level;
                var afterMenus = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).ToList();
                var targetAfterChilds = dbContext.menus.Where(m => m.up == targetMenu.up && m.sort > targetMenu.sort).ToList();
                foreach (var menuK in afterMenus)
                {
                    menuK.sort--;
                }
                //childMenus Update
                foreach (var menuK in targetAfterChilds)
                {
                    menuK.sort++;
                }
                //changeMenu update
                menu.up = targetMenu.up;
                menu.level = targetMenu.level;
                menu.sort = targetMenu.sort + 1;
                menu.update = DateTime.Now;

                //childMenus Update
                if (levelLength != 0)
                {
                    var allChildMenuIds = GetChildMenuId(dbContext, menu.id);
                    dbContext.menus
                        .Where(m => allChildMenuIds.Contains(m.id))
                        .ExecuteUpdate(setters => setters.SetProperty(m => m.level, m => m.level + levelLength));
                }
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> updateMenuNameById(int targetId, string newName,Result<int> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                //table null check
                if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
                //changeMenu exist check
                MenuEntity menu = dbContext.menus.FirstOrDefault(m => m.id == targetId);
                if (menu == null) { return res.setStatus(ResultCode.databaseNameExist); }
                //name exist checks
                bool nameExsit = dbContext.menus.Any(m => m.up == menu.up && m.name == newName);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //date init
                menu.name = newName;
                //commond
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<Dictionary<int, MenuEntity>> queryMenu(Result<Dictionary<int, MenuEntity>> res)
        {
            using (var scope=DbContextTool.GetAppDbContext(res.table,out var dbMay))
            {
                mapper.queryMenu(res, dbMay);
            }
            return res;
        }
        public Result<Dictionary<int, MenuEntity>> queryMenuAround(Result<Dictionary<int, MenuEntity>> res) {
            /**
                id递归			
	                limit 3w		
	                s	s<3w？	
                id的sort>1			
	                id前递归		
		                limit 3w-s1	
		                s=s+s1	s<3w？
                id的sort+1存在？			
	                id后递归		
		                limit 3w-s	
		                s=s+s2	s<3w？
                循环直到当前id同辈递归结束			
                s<3w?			
                继续向上扩散查询，第一选择是当前父级的左右节点			
                采用limit递归得到父级左右节点的s			
                遍历父级类依旧不够3w，则继续向上扩散，直至得到limit完成			
                注意的是，向y上扩散需要从sort极大值开始递减查询，向y下扩散需要从sort最小开始递增查询			
             */
            Stopwatch t = Stopwatch.StartNew();
            int id = res.id;
            string table = res.table;
            //id = 198;table = "menu";
            if (string.IsNullOrWhiteSpace(table)) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            using (var scope = DbContextTool.GetAppDbContext(table, out var dbContext))
            {
                
                if (id < menuCore.idStart)
                {
                    if (id == 0)
                    {
                        MenuEntity menuH = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.up == menuCore.upHead);
                        if (menuH == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                        id = menuH.id;
                    }
                    else {
                        return res.setStatus(ResultCode.menuIdAreaFalse);
                    }
                }
                
                Result<List<MenuEntity>> resA = new();
                resA.data = new List<MenuEntity>();
                resA.table = res.table;
                resA.id = id;
                List<MenuEntity> menuHead = new();
                try
                {
                    MenuEntity menu = new MenuEntity();
                    //目标id子代
                    menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == id); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                    queryMenuChildsById(resA, dbContext);
                    menuHead.Add(menu);
                    while (resA.data.Count < menuCore.queryLength)
                    {
                        //目标id 存储
                        if (menu.name=="学习方法") {
                            var a = 1;
                        }
                        resA.data.Add(menu);
                        if (menuHead.Count==0) { menuHead.Add(menu); }
                        //目标id，同辈（sortMax，count）
                        int sortM = dbContext.menus.Where(m => m.up == menu.up).Select(m => m.sort).Max(); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                        int count = dbContext.menus.Where(m => m.up == menu.up).Count(); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                        int afterLength = sortM - menu.sort;
                        int i = 1;
                        while (count > 1)
                        {
                            if (menu.sort < i&& afterLength<i ) {
                                //如果左右散列均到底，数据却没查完，说明sort不连续或出现负数
                                if (count > 1) {return res.setStatus(ResultCode.exceptionStatus); }
                                break;
                            }
                            //目标id左右扩散
                            if (menu.sort > i)
                            {
                                MenuEntity menuBefore = dbContext.menus.FirstOrDefault(m => m.up == menu.up && m.sort == menu.sort - i);
                                if (menuBefore != null) {
                                    resA.data.Add(menuBefore);
                                    menuHead.Add(menuBefore);
                                    resA.id = menuBefore.id;
                                    queryMenuChildsById(resA, dbContext);
                                    count--;
                                    if (resA.data.Count >= menuCore.queryLength) { break; }
                                }
                            }
                            if (i <= afterLength)
                            {
                                MenuEntity menuAfter = dbContext.menus.FirstOrDefault(m => m.up == menu.up && m.sort == menu.sort + i);
                                if (menuAfter != null) {
                                    resA.data.Add(menuAfter);
                                    menuHead.Add(menuAfter);
                                    resA.id = menuAfter.id;
                                    queryMenuChildsById(resA, dbContext);
                                    count--;
                                    if (resA.data.Count >= menuCore.queryLength) { break; }
                                }
                            }
                            i++;
                        }
                        if (resA.data.Count >= menuCore.queryLength) { 
                            break; }

                        menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == menu.up); if (menu == null) { break; } else { id = menu.id; }
                        menuHead.Clear();
                    }
                    res.data = resA.data.ToDictionary(m => m.id);
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
                menuHead.Sort((a,b)=>
                    a.sort.CompareTo(b.sort)
                );
                res.menuHeads=menuHead;
            }
            t.Stop();
            long ms = t.ElapsedMilliseconds;
            TimeSpan ts = t.Elapsed;
            Console.WriteLine($"{ts.Seconds}:{ts.Milliseconds}");
            return res;
            
        }
        public Result<Dictionary<int, MenuEntity>> queryMenuTop(Result<Dictionary<int, MenuEntity>> res)
        {
            Stopwatch t = Stopwatch.StartNew();
            int id = res.id;
            string table = res.table;
            //id = 198;table = "menu";
            if (string.IsNullOrWhiteSpace(table)) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            using (var scope = DbContextTool.GetAppDbContext(table, out var dbContext))
            {

                if (id < menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
                Result<List<MenuEntity>> resA = new() { table = res.table, id = id }; resA.data = new(); List<MenuEntity> menuHead = new();
                try
                {
                    MenuEntity menu = new MenuEntity();
                    menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == id); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                    while (resA.data.Count < menuCore.queryLength)
                    {
                        int count = dbContext.menus.Where(m => m.up == menu.up&&m.sort<menu.sort).Count(); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                        int i = 1;
                        int s = count;
                        while (count > 0)
                        {
                            if (s < 0 && count > 0)
                            {
                                return res.setStatus(ResultCode.exceptionStatus, "底部扩散查询死循环");
                            }
                            MenuEntity menuBefore = dbContext.menus.FirstOrDefault(m => m.up == menu.up && m.sort == menu.sort - i);
                            if (menuBefore != null)
                            {
                                resA.data.Add(menuBefore); menuHead.Add(menuBefore); resA.id = menuBefore.id;
                                queryMenuChildsById(resA, dbContext); count--; if (resA.data.Count >= menuCore.queryLength) { break; }
                            }
                            s--;
                            i++;
                        }
                        if (resA.data.Count >= menuCore.queryLength)
                        {
                            break;
                        }
                        menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == menu.up); if (menu == null) { break; } else { id = menu.id; }
                        menuHead.Clear(); menuHead.Add(menu);resA.data.Add(menu);
                    }
                    res.data = resA.data.ToDictionary(m => m.id);
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
                menuHead.Sort((a, b) =>
                    a.sort.CompareTo(b.sort)
                );
                res.menuHeads = menuHead;
            }
            t.Stop();
            long ms = t.ElapsedMilliseconds;
            TimeSpan ts = t.Elapsed;
            Console.WriteLine($"{ts.Seconds}:{ts.Milliseconds}");
            return res;

        }
        public Result<Dictionary<int, MenuEntity>> queryMenuBottom(Result<Dictionary<int, MenuEntity>> res)
        {
            Stopwatch t = Stopwatch.StartNew();
            int id = res.id;
            string table = res.table;
            //id = 198;table = "menu";
            if (string.IsNullOrWhiteSpace(table)) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            using (var scope = DbContextTool.GetAppDbContext(table, out var dbContext))
            {

                if (id < menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
                Result<List<MenuEntity>> resA = new()
                {
                    table = res.table,
                    id = id,
                    data = new()
                }; List<MenuEntity> menuHead = [];
                try
                {
                    MenuEntity menu = new MenuEntity();
                    menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == id); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                    while (resA.data.Count < menuCore.queryLength)
                    {
                        int count = dbContext.menus.Where(m => m.up == menu.up && m.sort > menu.sort).Count(); if (menu == null) { return res.setStatus(ResultCode.databaseQueryIdNull); }
                        int i = 1;
                        int s = count;
                        while (count > 0)
                        {
                            if (s < 0 && count > 0)
                            {
                                return res.setStatus(ResultCode.exceptionStatus, "底部扩散查询死循环");
                            }
                            MenuEntity menuAfter = dbContext.menus.FirstOrDefault(m => m.up == menu.up && m.sort == menu.sort + i);
                            if (menuAfter != null)
                            {
                                resA.data.Add(menuAfter); menuHead.Add(menuAfter); resA.id = menuAfter.id;
                                queryMenuChildsById(resA, dbContext); count--; if (resA.data.Count >= menuCore.queryLength) { break; }
                            }
                            s--;
                            i++;
                        }
                        if (resA.data.Count >= menuCore.queryLength)
                        {
                            break;
                        }
                        menu = dbContext.menus.AsNoTracking().FirstOrDefault(m => m.id == menu.up); if (menu == null) { break; } else { id = menu.id; }
                        menuHead.Clear(); menuHead.Add(menu); resA.data.Add(menu);
                    }
                    res.data = resA.data.ToDictionary(m => m.id);
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
                menuHead.Sort((a, b) =>
                    a.sort.CompareTo(b.sort)
                );
                res.menuHeads = menuHead;
            }
            t.Stop();
            long ms = t.ElapsedMilliseconds;
            TimeSpan ts = t.Elapsed;
            Console.WriteLine($"{ts.Seconds}:{ts.Milliseconds}");
            return res;

        }
        public Result<MenuEntity> queryMenuById(Result<MenuEntity> res)
        {
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                try
                {
                    res.data = dbContext.menus.FirstOrDefault(m => m.id==res.id);
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
            }
            return res;
        }
        public Result<List<MenuEntity>> queryMenuSurroundsById(Result<List<MenuEntity>> res) { 
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext))
            {
                try
                {
                    var model = dbContext.menus;
                    //
                    var entitys = res.data;
                    if (entitys == null) {
                        entitys = new List<MenuEntity>();
                    }
                    //
                    var id = res.id;
                    //
                    var entity = model.FirstOrDefault(m => m.id == id);
                    if (entity != null) { entitys.Add(entity); }
                    //
                    var entityBefore = model
                        .Where(m => m.up == entity.up && m.sort < entity.sort)
                        .OrderByDescending(m => m.sort)
                        .FirstOrDefault();
                    if (entityBefore!=null) { entitys.Add(entityBefore);  }
                    //
                    var entityAfter = model
                        .Where(m => m.up == entity.up && m.sort > entity.sort)
                        .FirstOrDefault();
                    if (entityAfter != null) { entitys.Add(entityAfter);  }
                    //
                    var entityUp = model.FirstOrDefault(m=>m.id==entity.up);
                    if (entityUp != null) { entitys.Add(entityUp);  }
                    res.data = entitys;
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
            }
            return res;
        }
        public Result<List<MenuEntity>> queryMenuChildsById(Result<List<MenuEntity>> res) {
            int id = res.id;
            string MENU = res.table;
            using (var scope = DbContextTool.GetAppDbContext(res.table, out var dbContext)) {
                if (id <= menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
                try
                {
                    string UP = nameof(MenuEntity.up).ToUpper();
                    string ID = nameof(MenuEntity.id).ToUpper();
                    string sql = $@"
                        WITH RECURSIVE MENU_TREE AS(
                            SELECT * FROM {MENU} WHERE {UP}={id}
                            UNION ALL
                            SELECT M.* FROM {MENU} M JOIN MENU_TREE MT ON M.{UP} =MT.{ID}
                        )
                        SELECT * FROM MENU_TREE LIMIT {menuCore.queryLength}
                        ";
                    var allChilds = dbContext.menus.FromSqlRaw(sql).ToList();
                    //var allChilds = dbContext.menus.FromSql($@"
                    //    WITH RECURSIVE MENU_TREE AS(
                    //        SELECT * FROM {MENU} WHERE {UP}={id}
                    //        UNION ALL
                    //        SELECT M.* FROM {MENU} M JOIN MENU_TREE MT ON M.{UP} =MT.{ID}
                    //    )
                    //    SELECT * FROM MENU_TREE LIMIT {menuCore.queryLength}
                    //    ").ToList();
                    res.data.AddRange(allChilds);
                }
                catch (Exception ex)
                {
                    res.setStatus(ResultCode.exceptionStatus, ex.Message);
                }
            }
            return res;
        }
        public Result<List<MenuEntity>> queryMenuChildsById(Result<List<MenuEntity>> res,AppDbContext dbContext)
        {
            int id = res.id;
            string MENU = res.table;
            if (id <= menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            try
            {
                string UP = nameof(MenuEntity.up).ToUpper();
                string ID = nameof(MenuEntity.id).ToUpper();
                string sql = $@"
                        WITH RECURSIVE MENU_TREE AS(
                            SELECT * FROM {MENU} WHERE {UP}={id}
                            UNION ALL
                            SELECT M.* FROM {MENU} M JOIN MENU_TREE MT ON M.{UP} =MT.{ID}
                        )
                        SELECT * FROM MENU_TREE LIMIT {menuCore.queryLength}
                        ";
                var allChilds = dbContext.menus.FromSqlRaw(sql).ToList();
                //var allChilds = dbContext.menus.FromSql($@"
                //    WITH RECURSIVE MENU_TREE AS(
                //        SELECT * FROM {MENU} WHERE {UP}={id}
                //        UNION ALL
                //        SELECT M.* FROM {MENU} M JOIN MENU_TREE MT ON M.{UP} =MT.{ID}
                //    )
                //    SELECT * FROM MENU_TREE LIMIT {menuCore.queryLength}
                //    ").ToList();
                res.data.AddRange(allChilds);
            }
            catch (Exception ex)
            {
                res.setStatus(ResultCode.exceptionStatus, ex.Message);
            }
            return res;
        }
        public Result<Dictionary<int, List<MenuEntity>>> sortMenuIdMap(Result<Dictionary<int, MenuEntity>> req, Result<Dictionary<int, List<MenuEntity>>> res)
        {
            res.data = req.data.Values
                .GroupBy(menu => menu.up)
                .ToDictionary(
                    group => group.Key,
                    group => group.OrderBy(menu => menu.sort).ToList()
                );
            //var map = req.data.Values;

            //foreach (var item in map)
            //{
            //    if (res.data.ContainsKey(item.up))
            //    {
            //        res.data[item.up].Add(item);
            //    }
            //    else {
            //        res.data.Add(item.up,new List<MenuEntity> {item });
            //    }
            //}

            return res;
        }
        public List<int> GetChildMenuId(AppDbContext dbContext,int upMenuId) {
            var firstMenus = dbContext.menus
                .Where(m => m.up == upMenuId)
                .Select(m => m.id).ToList();
            var allMenus = new List<int>(firstMenus);
            foreach (var childId in firstMenus) {
                var childIds = GetChildMenuId(dbContext, childId);
                allMenus.AddRange(childIds);
            }
            return allMenus;
        }

        #endregion

        #region in_core
        public Result<List<MenuInCoreEntity>> queryMenuInCoreByMenuId(int menuId, Result<List<MenuInCoreEntity>> res) {
            //
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                List<MenuInCoreEntity> inCores = dbContext.inCores.Where(m => m.menuId == menuId).OrderBy(m => m.sort).ToList();
                if (inCores == null) { return res.setStatus(ResultCode.databaseNameExist); }
                res.data = inCores;
            }
            return res;
        }
        public Result<MenuInCoreEntity> AddMenuInCoreByMenuId(Result<MenuInCoreEntity> res)
        {
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            //
            MenuInCoreEntity entity = res.data;
            if (entity==null) { return res.setStatus(ResultCode.dataNull); }
            if (string.IsNullOrWhiteSpace(entity.attribute)) { return res.setStatus(ResultCode.dataNameNull); }
            //
            if (entity.id<menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            //
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                var model = dbContext.inCores;
                //
                bool nameExsit = model.Any(m => m.menuId == entity.menuId && m.attribute == entity.attribute);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //
                entity.create = DateTime.Now;
                entity.sort= Convert.ToInt32(model.Where(m=>m.menuId==entity.menuId).Select(m => (int?)m.sort).Max()) +1;
                //
                dbContext.Add(entity);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<MenuInCoreEntity> updateMenuInCoreById(Result<MenuInCoreEntity> res)
        {
            //table null
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            MenuInCoreEntity entity = res.data;
            //entity null
            if (entity == null) { return res.setStatus(ResultCode.dataNull); }
            //column null
            if (string.IsNullOrWhiteSpace(entity.attribute)) { return res.setStatus(ResultCode.dataNameNull); }
            //table assembly
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                var model = dbContext.inCores;
                //db id null
                MenuInCoreEntity dbEntity = model.FirstOrDefault(m => m.id == entity.id);
                if (dbEntity == null) { return res.setStatus(ResultCode.dataSelectNull); }
                //name exist checks
                bool nameExsit = model.Any(m => m.menuId == entity.menuId && m.attribute == entity.attribute);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                //value
                if (!string.IsNullOrWhiteSpace(entity.attribute)) { 
                    dbEntity.attribute = entity.attribute;
                }
                if (!string.IsNullOrWhiteSpace(entity.context))
                {
                    dbEntity.context = entity.context;
                }
                //save
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<MenuInCoreEntity> deleteMenuInCoreById(Result<MenuInCoreEntity> res) {
            //table null
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            MenuInCoreEntity entity = res.data;
            //entity null
            if (entity == null) { return res.setStatus(ResultCode.dataNull); }
            //column null
            if (entity.id<menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            //table assembly
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext)) {
                //model
                var entityModel = dbContext.inCores;
                //dbEntity
                entity = entityModel.Find(res.data.id);
                if (entity==null) { return res.setStatus(ResultCode.databaseDeleteDataNoSelect); }
                entityModel.Remove(entity);
                //save
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> changeBeforeInCore(int changeId,int targetId, Result<int> res) {
            //
            if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
            //table null
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                //model
                var model = dbContext.inCores;
                //dbEntity null
                MenuInCoreEntity entity = model.FirstOrDefault(o => o.id == changeId);
                if (entity == null) { return res.setStatus(ResultCode.dataSelectNull); }
                //dbEntity target null
                MenuInCoreEntity entityTarget = model.FirstOrDefault(o => o.id == targetId);
                if (entityTarget == null) { return res.setStatus(ResultCode.dataSelectNull); }
                //child 
                var entityAfters = model.Where(m => m.menuId == entity.menuId && m.sort > entity.sort).ToList();
                var entityTargetAfters = model.Where(m => m.menuId == entityTarget.menuId && m.sort >= entityTarget.sort).ToList();
                foreach (var item in entityAfters)
                {
                    item.sort--;
                }
                //child 
                foreach (var item in entityTargetAfters)
                {
                    item.sort++;
                }
                //value
                entity.sort = entityTarget.sort-1;
                entity.update = DateTime.Now;
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> changeAfterInCore(int changeId, int targetId, Result<int> res)
        {
            //
            if (changeId == targetId) { return res.setStatus(ResultCode.menuChangeNotHasMe); }
            //table null
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                //model
                var model = dbContext.inCores;
                //dbEntity null
                MenuInCoreEntity entity = model.FirstOrDefault(o => o.id == changeId);
                if (entity == null) { return res.setStatus(ResultCode.dataSelectNull); }
                //dbEntity target null
                MenuInCoreEntity entityTarget = model.FirstOrDefault(o => o.id == targetId);
                if (entityTarget == null) { return res.setStatus(ResultCode.dataSelectNull); }
                //child 
                var entityAfters = model.Where(m => m.menuId == entity.menuId && m.sort > entity.sort).ToList();
                var entityTargetAfters = model.Where(m => m.menuId == entityTarget.menuId && m.sort > entityTarget.sort).ToList();
                foreach (var item in entityAfters)
                {
                    item.sort--;
                }
                foreach (var item in entityTargetAfters)
                {
                    item.sort++;
                }
                //value
                entity.sort = entityTarget.sort + 1;
                entity.update = DateTime.Now;
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> addBeforeInCore(MenuInCoreEntity entity, int targetId, Result<int> res)
        {
            /**
              // table null check
              // entity null check
              // column[attribute] null check
              // targetId >= idStart
              // column[name] null check
              // target entity null check
              
             */

            // table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            // entity null check
            if (entity == null) { return res.setStatus(ResultCode.dataNull); }
            // column[attribute] null check
            if (string.IsNullOrWhiteSpace(entity.attribute)) { return res.setStatus(ResultCode.dataNameNull); }
            // targetId >= idStart
            if (targetId<menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            //
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                //model
                var model = dbContext.inCores;
                // target entity null
                MenuInCoreEntity entityTarget = model.FirstOrDefault(o => o.id == targetId);
                if (entityTarget == null) { return res.setStatus(ResultCode.dataSelectNull); }
                // column[name] null check
                bool nameExsit = model.Any(m => m.menuId == entityTarget.menuId && m.attribute == entity.attribute);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                MenuInCoreEntity newEntity = new() {
                    menuId = entityTarget.menuId,
                    attribute = entity.attribute,
                    context= entity.context,
                    sort = entityTarget.sort,
                    create = DateTime.Now
                };
                //child 
                var entityTargetAfters = model.Where(m => m.menuId == entityTarget.menuId && m.sort >= entityTarget.sort).ToList();
                foreach (var item in entityTargetAfters)
                {
                    item.sort++;
                }
                //value
                dbContext.Add(newEntity);
                dbContext.SaveChanges();
            }
            return res;
        }
        public Result<int> addAfterInCore(MenuInCoreEntity entity, int targetId, Result<int> res)
        {
            /**
              // table null check
              // entity null check
              // column[attribute] null check
              // targetId >= idStart
              // column[name] null check
              // target entity null check
              
             */

            // table null check
            if (string.IsNullOrWhiteSpace(res.table)) return res.setStatus(ResultCode.databaseTableCheckError);
            // entity null check
            if (entity == null) { return res.setStatus(ResultCode.dataNull); }
            // column[attribute] null check
            if (string.IsNullOrWhiteSpace(entity.attribute)) { return res.setStatus(ResultCode.dataNameNull); }
            // targetId >= idStart
            if (targetId < menuCore.idStart) { return res.setStatus(ResultCode.menuIdAreaFalse); }
            //
            string tempTable = menuCore.inCore + menuCore.split + res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext))
            {
                //model
                var model = dbContext.inCores;
                
                // target entity null
                MenuInCoreEntity entityTarget = model.FirstOrDefault(o => o.id == targetId);
                if (entityTarget == null) { return res.setStatus(ResultCode.dataSelectNull); }
                // column[name] null check
                bool nameExsit = model.Any(m => m.menuId == entityTarget.menuId && m.attribute == entity.attribute);
                if (nameExsit) { return res.setStatus(ResultCode.databaseNameExist); }
                MenuInCoreEntity newEntity = new()
                {
                    menuId = entityTarget.menuId,
                    attribute = entity.attribute,
                    context = entity.context,
                    sort = entityTarget.sort,
                    create = DateTime.Now
                };
                //child 
                var entityTargetAfters = model.Where(m => m.menuId == entityTarget.menuId && m.sort > entityTarget.sort).ToList();
                foreach (var item in entityTargetAfters)
                {
                    item.sort++;
                }
                //value
                dbContext.Add(newEntity);
                dbContext.SaveChanges();
            }
            return res;
        }
        #endregion

        #region 
        /**
         * parameter: string key
         * Result: table, schema, 
         */
        public Result<List<string>> searchTableByKeyword(string key, Result<List<string>> res)
        {
            //CONCAT(TABLE_NAME,'-',TABLE_SCHEMA)
            var sql = $@"
                        SELECT TABLE_NAME
                        FROM INFORMATION_SCHEMA.TABLES
                        WHERE TABLE_SCHEMA=@SCHEMA
                            AND TABLE_NAME LIKE @KEYWORD
                            AND TABLE_TYPE ='BASE TABLE'
                        ";
            string tempTable =  res.table;
            using (var scope = DbContextTool.GetAppDbContext(tempTable, out var dbContext)) {
                //model
                var model = dbContext.Database;
               res.data=model.SqlQueryRaw<string>(sql
                   ,
                    new MySqlParameter("@SCHEMA", res.schema),
                    new MySqlParameter("@KEYWORD", key)
                   )
                   .AsNoTracking().ToList();
            }
            return res;
        }

       
        #endregion
    }
}